﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CH.Easy.DbProvider.Interface;
using CH.Easy.DbProvider.Entity;
using CH.Easy.DbProvider.Common;
using System.Data;
using System.Data.Common;
using CH.Easy.DbProvider.Mapping;
using System.Reflection;

namespace CH.Easy.DbProvider.Core
{
    public class TSQLEngine
    {
        public IDbTable<Default> Table { get; set; }
        public TSQLEngine()
        {

        }
        public TSQLEngine(DbProviderType dbProviderType)
        {
            Table = new DbTable<Default>(dbProviderType);
        }

        public bool CreateTable<TEntity>()
        {
            Type t = typeof(TEntity);
            string tableName = t.Name;
            string primaryKey = "ID";
            bool isIdentity = false;
            var attributes = t.GetCustomAttributes(typeof(TableMappingAttribute), true);
            if (attributes != null && attributes.Count() > 0)
            {
                foreach (var attribute in attributes)
                {
                    TableMappingAttribute a = attribute as TableMappingAttribute;
                    if (a != null)
                    {

                        tableName = a.Name;
                        primaryKey = a.PrimaryKey;
                        isIdentity = a.IsIdentity;
                    }
                }
            }

            StringBuilder sql = new StringBuilder("CREATE TABLE [dbo].[" + tableName + "](" + Environment.NewLine);
            if (isIdentity)
            {
                sql.Append("[" + primaryKey + "] [int] IDENTITY(1,1) NOT NULL,");
            }

            var ps = t.GetProperties();
            foreach (PropertyInfo p in ps)
            {
                if (isIdentity)
                {
                    if (p.Name == primaryKey)
                    {
                        continue;
                    }

                }
                if (p.GetType() == typeof(string))
                {
                    sql.Append("[" + p.Name + "] [nvarchar](50)," + Environment.NewLine);
                }
                else
                {
                    sql.Append("[" + p.Name + "] [" + p.PropertyType.ToSqlDbType().ToString().ToLower() + "] NULL," + Environment.NewLine);
                }
            }

            sql = sql.Remove(sql.Length - 1, 1);
            sql.Append(")");

            this.ExecuteSQL(sql.ToString());

            return true;
        }
        public bool DropTable<TEntity>()
        {
            return true;
        }


        public object ExecuteSQL(string sql, params DbParameter[] parameter)
        {

            DbTableProvider provider = Table.Provider as DbTableProvider;
            return provider.ExecuteSQLCommand(sql, ExecuteType.ExecuteScalar, parameter);
        }
        public object ExecuteSQL(string sql, ExecuteType executeType, params DbParameter[] parameter)
        {

            DbTableProvider provider = Table.Provider as DbTableProvider;
            return provider.ExecuteSQLCommand(sql, executeType, parameter);
        }

        public DataTable ExecuteStoredProcedure(string spName, params DbParameter[] parameter)
        {
            DbTableProvider provider = Table.Provider as DbTableProvider;
            return (DataTable)provider.ExecuteStoredProcedureCommand(spName, ExecuteType.ExecuteDataTable, parameter);
        }

        public object ExecuteSQL(string sql, string connectionKey, params DbParameter[] parameter)
        {
            DbTableProvider provider = Table.Provider as DbTableProvider;
            provider.TableName = connectionKey;
            return provider.ExecuteSQLCommand(sql, ExecuteType.ExecuteScalar, parameter);
        }
        public object ExecuteSQL(string sql, string connectionKey, ExecuteType executeType, params DbParameter[] parameter)
        {

            DbTableProvider provider = Table.Provider as DbTableProvider;
            provider.TableName = connectionKey;
            return provider.ExecuteSQLCommand(sql, executeType, parameter);
        }
        public DataTable ExecuteStoredProcedure(string spName, string connectionKey, params DbParameter[] parameter)
        {
            DbTableProvider provider = Table.Provider as DbTableProvider;
            provider.TableName = connectionKey;
            return (DataTable)provider.ExecuteStoredProcedureCommand(spName, ExecuteType.ExecuteDataTable, parameter);
        }
    }
}
